USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0005_BASE]    Script Date: 11/28/2011 11:58:41 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WASP_M0005_BASE]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WASP_M0005_BASE]
GO

USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0005_BASE]    Script Date: 11/28/2011 11:58:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[WASP_M0005_BASE]
AS

---------TOTAL CARTERA NO SECURITIZADO---------------
BEGIN
			SELECT 
				SCCTA, 
				SCOPER,
				Scsuc, 
				SCRUB, 
				J50PCCBNT, 
				J50PCCPS, 
				SUM (SCSDO)suma,
				'N' SECURITIZADO,
				NULL AS sec01ptcta
			INTO dbo.base  
			FROM 
				Fsd011 A (NOLOCK), 
				J50PCC B (NOLOCK) 
			WHERE 
				------- CAPITAL
				((SCRUB between '1116001000' AND '1116005000') OR	
				(SCRUB BETWEEN '1310001000' AND '1310005000')  OR
				(SCRUB between '1130001000' AND '1130004000')  OR
				(SCRUB BETWEEN '1330001000' AND '1330005000')  OR
				(SCRUB between '1140001000' AND '1140009000')  OR
				(SCRUB BETWEEN '1340001000' AND '1340005000')  OR
				(SCRUB BETWEEN '1340011000' AND '1340015000')  OR
				(SCRUB between '1150001000' AND '1150009000')  OR
				(SCRUB BETWEEN '1350001000' AND '1350005000')  OR
				(SCRUB BETWEEN '1350011000' AND '1350015000') OR

			--------- INTERES
				(SCRUB between '1161001000' AND '1161005000')  OR
				(SCRUB BETWEEN '1361001000' AND '1361005000')  OR
				(SCRUB between '1163001000' AND '1163005000')  OR
				(SCRUB BETWEEN '1363001000' AND '1363005000')  OR
				(SCRUB between '1164001000' AND '1164009000')  OR
				(SCRUB BETWEEN '1364001000' AND '1364009000')  OR
				(SCRUB between '1165001000' AND '1165009000')  OR
				(SCRUB BETWEEN '1365001000' AND '1370000000')  OR

				--------- REAJUSTE

				(SCRUB BETWEEN '1171002000' AND '1171004500')  OR
				(SCRUB BETWEEN '1371002000' AND '1371004500')  OR
				(SCRUB BETWEEN '1173002000' AND '1173004500')  OR
				(ScRUB BETWEEN '1373002000' AND '1373004500')  OR
				(SCRUB BETWEEN '1175002000' AND '1175008500')  OR
				(SCRUB BETWEEN '1373102000' AND '1373114500')  OR
				(SCRUB BETWEEN '1177002000' AND '1177008500')  OR
				(SCRUB BETWEEN '1373202000' AND '1373214500') )  AND
				SCRUB=J50PCCBNT AND SCSDO<>0 
				
			GROUP BY SCCTA, SCOPER,Scsuc,SCRUB,J50PCCPS, J50PCCBNT
			UNION
			SELECT 
				SCCTA, 
				SCOPER,
				Scsuc,
				SCRUB, 
				J50PCCBNT, 
				J50PCCPS, 
				SUM (SCSDO)suma,
				'S'SECURITIZADO ,
				sec01ptcta
			FROM 
				Fsd011 A (NOLOCK), 
				FSEC10 B (NOLOCK), 
				J50PCC C (NOLOCK) 
			WHERE
			-- Capital
				(  (SCRUB between '8816001000' AND '8830000000')  OR
				(SCRUB BETWEEN '8830001000' AND '8840000000')  OR
				(SCRUB BETWEEN '8840001000' AND '8840010000')  OR
				(SCRUB between '8850001000' AND '8860000000')  OR
				(scrub between 8910000000 and 8910004999) or
				(scrub between 8930000000 and 8930004999) or 
				(scrub between 8940000000 and 8940014999) or
				(scrub between 8950000000 and 8950014999)or
			--Interés
				(SCRUB BETWEEN '8861001000' AND '8863000000')  OR
				(SCRUB BETWEEN '8863001000' AND '8863004400')  OR
				(SCRUB BETWEEN '8864001000' AND '8865000000')  OR
				(SCRUB BETWEEN '8865001000' AND '8870000000') OR
				(scrub between '8961001000' and '8963000000') or
				(scrub between '8963001000' and '8964000000') or
				(scrub between '8964001000' and '8965000000' ) or
				(scrub between '8965001000' and '8970000000')) AND 
				A.SCCTA = B.Sec10AoCta AND
				A.SCOPER = B.Sec10AoOpe AND
				A.SCSUC = B.Sec10AoSuc AND
				--B.sec10stspt = 9 AND
				A.SCRUB=C.J50PCCBNT AND 
				B.sec01ptcta = C.J50PCCOPE AND
				A.SCSDO<>0 
					
			GROUP BY SCCTA, SCOPER, Scsuc,SCRUB,J50PCCPS, J50PCCBNT,sec01ptcta
END			

GO

